drop table jms_dm.dm_mng_cn_project_network_summary_dt;
create table jms_dm.dm_mng_cn_project_network_summary_dt
(
    project_name          varchar(100) comment '项目名称',
    customer_network_code varchar(100) comment '客户所属网点code',
    customer_agent_code   varchar(100) comment '客户所属代理区code',
    customer_network_name varchar(100) comment '客户所属网点name',
    customer_center_code  varchar(100) comment '客户所属中心code',
    customer_center_name  varchar(100) comment '客户所属中心name',
    customer_agent_name   varchar(100) comment '客户所属代理区name',
    order_total           int comment '订单总量',
    need_taking_num       int comment '应揽收订单量',
    cancel_num            int comment '取消量',
    actual_taking_num     int comment '实际揽收量',
    no_taking_num         int comment '未揽收量',
    timely_taking_num     int comment '及时揽收量',
    timely_taking_rate    float comment '揽收及时率',
    express_num           int comment '破损量',
    arbitration_nun       int comment '遗失量',
    need_sign_num         int comment '应签收量',
    have_sign_num         int comment '签收及时量',
    no_sign_num           int comment '未签收量',
    have_sign_rate        float comment '签收及时率',
    m6_no_sign_num        int comment '超6天未签收量',
    m7_no_sign_num        int comment '超7天未签收量',
    type                  int comment '类型,1是录入时间统计数据,2是揽收时效时间统计数据',
    date_time             date comment '日期'
) ENGINE = OLAP DUPLICATE KEY(project_name,customer_network_code,customer_agent_code)
COMMENT '菜鸟项目报表汇总'
PARTITION BY RANGE(date_time)
(START ('2022-08-01') END ('2023-03-01') EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(date_time) BUCKETS 1
PROPERTIES (
'replication_num' = '3',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'DAY',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-730',
'dynamic_partition.end' = '3',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '1',
'in_memory' = 'false',
'storage_format' = 'V2'
);
